package com.frontend;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.data.documents.Appointment;
import com.data.documents.DoctorsOrder;
import com.data.documents.Invoice;
import com.data.documents.PatientInformation;
import com.data.documents.TreatmentRecord;
import com.data.documents.UserDocument;
import com.users.User;

/**
 * Serves as an interface between the main system and the data storage backend.
 * Provides methods to access and modify documents, users, etc.
 * 
 * @author Elizabeth
 * @author Taylor
 */
public class NetworkInterfacer {

	private Connection con;
	private Logger logger;

	/**
	 * Once networking is enabled
	 * 
	 * @param serverAddress
	 * @param portNumber
	 */
	public NetworkInterfacer(Logger logger) throws ConnectionException {
		try {
			con = DriverManager.getConnection(
					"jdbc:mysql://james.shroffabhishek.com/doctorsoffice"
							+ "?holdResultsOpenOverStatementClose=true",
					"doctorsoffice", "doctorsoffice4Upt4hMQw7FK3dZM");
		} catch (SQLException e) {
			throw new ConnectionException(e, e.getMessage());
		}

		System.out.println("Connected :)");
		
		this.logger = logger;

		// con = DriverManager.getConnection(
		// "jdbc:mysql://james.shroffabhishek.com/doctorsoffice"
		// + "?holdResultsOpenOverStatementClose=true", "root",
		// "gammacentauri");
	}

	/**
	 * Log the user into the system
	 * 
	 * @param username
	 *            the user's username
	 * @param password
	 *            the user's password
	 * @return User that is logged in
	 * @throws ConnectionException
	 * @throws LockoutException
	 */
	public User login(String username, String password)
			throws ConnectionException, LockoutException {
		ResultSet rs = null;
		User u = null;
		String query = "SELECT user_type FROM users WHERE username = ? ;";
		PreparedStatement p;
		try {
			p = con.prepareStatement(query);
			p.setString(1, username);
			rs = p.executeQuery();
		} catch (SQLException e1) {
			try {
				reconnect();
				p = con.prepareStatement(query);
				p.setString(1, username);
				rs = p.executeQuery();
			} catch (SQLException re1) {
				System.out.println(e1.getMessage());
			}
		}

		int user_type = 0;
		boolean loop = true;

		while (loop) {
			try {
				if (rs.next()) {
					user_type = rs.getInt("user_type");
					int attempts = user_type >> 3;
					if (attempts == 3)
						throw new LockoutException();
					else {
						user_type = ((user_type & 7) | ((attempts + 1) << 3));
						query = "UPDATE users SET user_type = ? WHERE username = ? ;";
						p = con.prepareStatement(query);
						p.setInt(1, user_type);
						p.setString(2, username);
						p.executeUpdate();
					}
				}

				query = "SELECT * FROM users WHERE username = ? AND passwd = SHA( ? );";
				p = con.prepareStatement(query);
				p.setString(1, username);
				p.setString(2, password);
				rs = p.executeQuery();

				user_type = 0;
				String name = null;
				boolean found = false;
				if (rs.next()) {
					found = true;
					user_type = rs.getInt("user_type");
					name = rs.getString("name");
					user_type = user_type & 7;
					query = "UPDATE users SET user_type = ? WHERE username = ? ;";
					p = con.prepareStatement(query);
					p.setInt(1, user_type);
					p.setString(2, username);
					p.executeUpdate();
				}

				if (!found)
					return null;

				u = new User(name, username, (user_type & 7),
						(user_type >> 3) >= 3);
				loop = false;
			} catch (SQLException e1) {
				System.out.println(e1.getMessage());
			}
		}
		return u;
	}

	/**
	 * Unlocks a user that has been locked out of the system
	 * 
	 * @param username
	 *            The username of the user who was locked out
	 * @throws ConnectionException
	 */
	public void unlock(String username) throws ConnectionException {
		ResultSet rs = null;
		String query = "SELECT user_type FROM users WHERE username = ? ;";

		PreparedStatement p;
		try {
			p = con.prepareStatement(query);

			p.setString(1, username);
			rs = p.executeQuery();

			int user_type = 0;

			while (rs.next()) {
				user_type = rs.getInt("user_type");
			}

			user_type = (7 & user_type);

			query = "UPDATE users SET user_type = ? WHERE username = ? ;";
			p = con.prepareStatement(query);
			p.setInt(1, user_type);
			p.setString(2, username);
			p.executeUpdate();

		} catch (SQLException e) {
			try {
				reconnect();
				p = con.prepareStatement(query);

				p.setString(1, username);
				rs = p.executeQuery();

				int user_type = 0;

				while (rs.next()) {
					user_type = rs.getInt("user_type");
				}

				user_type = (7 & user_type);

				query = "UPDATE users SET user_type = ? WHERE username = ? ;";
				p = con.prepareStatement(query);
				p.setInt(1, user_type);
				p.setString(2, username);
				p.executeUpdate();

			} catch (SQLException re) {
				System.out.println(e.getMessage());
			}
		}
	}

	/**
	 * Locks a users account. Only admins can suspend users
	 * 
	 * @param username
	 *            The username of the user to suspend
	 * @throws ConnectionException
	 */
	public void suspend(String username) throws ConnectionException {
		ResultSet rs = null;
		String query = "SELECT user_type FROM users WHERE username = ? ;";

		PreparedStatement p;
		try {
			p = con.prepareStatement(query);

			p.setString(1, username);
			rs = p.executeQuery();

			int user_type = 0;

			while (rs.next()) {
				user_type = rs.getInt("user_type");
			}

			user_type = ((3 << 3) | (user_type & 7));

			query = "UPDATE users SET user_type = ? WHERE username = ? ;";
			p = con.prepareStatement(query);
			p.setInt(1, user_type);
			p.setString(2, username);
			p.executeUpdate();
		} catch (SQLException e) {
			try {
				reconnect();
				p = con.prepareStatement(query);

				p.setString(1, username);
				rs = p.executeQuery();

				int user_type = 0;

				while (rs.next()) {
					user_type = rs.getInt("user_type");
				}

				user_type = ((3 << 3) | (user_type & 7));

				query = "UPDATE users SET user_type = ? WHERE username = ? ;";
				p = con.prepareStatement(query);
				p.setInt(1, user_type);
				p.setString(2, username);
				p.executeUpdate();
			} catch (SQLException re) {
				System.out.println(e.getMessage());
			}
		}
	}

	/**
	 * Delete the user with the given username, and all documents tied to them
	 * 
	 * @param username
	 * @throws ConnectionException
	 */
	public void deleteUser(String username) throws ConnectionException {
		String query = "SELECT doc_id,doc_type FROM documents WHERE username = ? ;";

		PreparedStatement p;
		ResultSet rs = null;

		try {
			p = con.prepareStatement(query);
			p.setString(1, username);
			rs = p.executeQuery();
		} catch (SQLException e) {
			try {
				reconnect();
				p = con.prepareStatement(query);
				p.setString(1, username);
				rs = p.executeQuery();
			} catch (SQLException re) {
				System.out.println(e.getMessage());
			}
		}
		
		ArrayList<String> docTypes = new ArrayList<String>();
		ArrayList<String> docIds = new ArrayList<String>();
		
		try{
		while(rs.next()){
			docTypes.add(rs.getString("doc_type"));
			docIds.add(rs.getString("doc_id"));
		}
		} catch(Exception e){}

		try {
			while (docTypes.size() != 0) {
				String table = docTypes.remove(0);
				String id = docIds.remove(0);
				query = "DELETE FROM " + table + " WHERE doc_id = " + id + " ;";
				p = con.prepareStatement(query);
				p.executeUpdate();
			}
		} catch (SQLException e) {
			try {
				while (docTypes.size() != 0) {
					String table = docTypes.remove(0);
					String id = docIds.remove(0);
					query = "DELETE FROM " + table + " WHERE doc_id = " + id + " ;";
					System.out.println(query);
					p = con.prepareStatement(query);
					p.executeUpdate();
				}
			} catch (SQLException re) {
				System.out.println(e.getMessage());
			}
		}
		
		query = "DELETE FROM documents WHERE username = ? ; ";
		
		try {
			p = con.prepareStatement(query);
			p.setString(1, username);
			p.executeUpdate();
		} catch (SQLException e) {
			try {
				reconnect();
				p = con.prepareStatement(query);
				p.setString(1, username);
				p.executeUpdate();
			} catch (SQLException re) {
				System.out.println(e.getMessage());
			}
		}

		query = "DELETE FROM users WHERE username = ? ;";

		try {
			p = con.prepareStatement(query);
			p.setString(1, username);
			p.executeUpdate();
		} catch (SQLException e) {
			try {
				reconnect();
				p = con.prepareStatement(query);
				p.setString(1, username);
				p.executeUpdate();
			} catch (SQLException re) {
				System.out.println(e.getMessage());
			}
		}
	}

	/**
	 * Search for users based on given criteria
	 * 
	 * @param search
	 *            The criteria by which to search for users
	 * @return An array of Users that matches the search parameters
	 * @throws ConnectionException
	 */
	public User[] searchNames(String search) throws ConnectionException {
		ArrayList<User> results = new ArrayList<User>();

		ResultSet rs = null;
		String query = "SELECT * FROM users;";
		PreparedStatement p;
		try {
			p = con.prepareStatement(query);

			rs = p.executeQuery();
			if (rs != null) {
				while (rs.next()) {
					String name = rs.getString("name");
					String username = rs.getString("username");
					int user_type = rs.getInt("user_type");
					if (name.toLowerCase().contains(search.toLowerCase())
							| username.toLowerCase().contains(
									search.toLowerCase())) {
						results.add(new User(name, username, (user_type & 7),
								(user_type >> 3) >= 3));
					}
				}
			}
		} catch (SQLException e) {
			try {
				reconnect();
				p = con.prepareStatement(query);

				rs = p.executeQuery();
				if (rs != null) {
					while (rs.next()) {
						String name = rs.getString("name");
						String username = rs.getString("username");
						int user_type = rs.getInt("user_type");
						if (name.toLowerCase().contains(search.toLowerCase())
								| username.toLowerCase().contains(
										search.toLowerCase())) {
							results.add(new User(name, username,
									(user_type & 7), (user_type >> 3) >= 3));
						}
					}
				}
			} catch (SQLException re) {
				System.out.println(e.getMessage());
			}
		}
		User[] resultArr = new User[results.size()];
		resultArr = results.toArray(resultArr);
		return resultArr;
	}

	/**
	 * Add a user to the system
	 * 
	 * @param name
	 * @param username
	 * @param passwd
	 * @param user_type
	 * @return
	 * @throws ConnectionException
	 */
	public boolean addUser(String name, String username, String passwd,
			int user_type) throws ConnectionException {
		ResultSet rs = null;

		String query = "SELECT username FROM users;";
		PreparedStatement p;
		try {
			p = con.prepareStatement(query);

			rs = p.executeQuery();

			ArrayList<String> unames = new ArrayList<String>();
			while (rs.next())
				unames.add(rs.getString("username"));

			if (unames.contains(username))
				return false;

			query = "INSERT INTO users VALUES( ? , ? , SHA( ? ) , ? );";
			p = con.prepareStatement(query);
			p.setString(1, name);
			p.setString(2, username);
			p.setString(3, passwd);
			p.setInt(4, user_type);
			p.executeUpdate();
		} catch (SQLException e) {
			try {
				reconnect();
				p = con.prepareStatement(query);

				rs = p.executeQuery();

				ArrayList<String> unames = new ArrayList<String>();
				while (rs.next())
					unames.add(rs.getString("username"));

				if (unames.contains(username))
					return false;

				query = "INSERT INTO users VALUES( ? , ? , SHA( ? ) , ? );";
				p = con.prepareStatement(query);
				p.setString(1, name);
				p.setString(2, username);
				p.setString(3, passwd);
				p.setInt(4, user_type);
				p.executeUpdate();
			} catch (SQLException re) {
				System.out.println(e.getMessage());
			}
		}
		return true;
	}

	/**
	 * Changes the password of the current User
	 * 
	 * @param user
	 *            current User in the system
	 * @param newPassword
	 */
	public void changeUserPassword(String username, String newPassword)
			throws ConnectionException {
		String query = "UPDATE users SET passwd = SHA( ? ) WHERE username = ? ;";
		try {
			PreparedStatement p = con.prepareStatement(query);
			p.setString(1, newPassword);
			p.setString(2, username);
			p.executeUpdate();
		} catch (SQLException e) {
			try {
				reconnect();
				PreparedStatement p = con.prepareStatement(query);
				p.setString(1, newPassword);
				p.setString(2, username);
				p.executeUpdate();
			} catch (SQLException re) {
				throw new ConnectionException(e);
			}
		}
	}

	/**
	 * Checks to make sure the information input when changing passwords matches
	 * that of the user.
	 * 
	 * @param user
	 * @param password
	 * @return
	 * @throws SQLException
	 */
	public boolean authenticate(String username, String password)
			throws ConnectionException {
		String query = "SELECT * from users WHERE username = ? AND passwd = SHA( ? ) ;";
		boolean b = false;
		PreparedStatement p;
		try {
			p = con.prepareStatement(query);
			p.setString(2, password);
			p.setString(1, username);
			ResultSet rs = p.executeQuery();
			b = rs.next();
		} catch (SQLException e) {
			try {
				reconnect();
				p = con.prepareStatement(query);
				p.setString(2, password);
				p.setString(1, username);
				ResultSet rs = p.executeQuery();
				b = rs.next();
			} catch (SQLException re) {
				throw new ConnectionException(e);
			}
		}

		return b;
	}

	/**
	 * Lists all the doctors available in the system
	 * 
	 * @return a list of doctors
	 * @throws ConnectionException
	 */
	public String[] listUsersNames(int userType) throws ConnectionException {

		ResultSet rs = null;
		ArrayList<String> users = new ArrayList<String>();
		String query = "SELECT * FROM users;";
		PreparedStatement p;
		try {
			p = con.prepareStatement(query);
			rs = p.executeQuery();
			if (rs != null) {
				while (rs.next()) {

					if ((rs.getInt("user_type") & 7) == userType)
						users.add(rs.getString("name"));
				}
			}
		} catch (SQLException e) {
			try {
				reconnect();
				p = con.prepareStatement(query);
				rs = p.executeQuery();
				if (rs != null) {
					while (rs.next()) {

						if ((rs.getInt("user_type") & 7) == userType)
							users.add(rs.getString("name"));
					}
				}
			} catch (SQLException re) {
				throw new ConnectionException(e);
			}
		}

		String[] userNames = new String[users.size()];
		userNames = users.toArray(userNames);
		Arrays.sort(userNames);
		return userNames;
	}

	/**
	 * Updates the Appointment or creates a new one
	 * 
	 * @throws ConnectionException
	 */
	/**
	 * Gets the doctor that has the given name
	 * 
	 * @param name
	 *            Doctor's name
	 * @return the Doctor User
	 * @throws ConnectionException
	 */
	public User getUser(String name, int userType) throws ConnectionException {
		String query = "SELECT * FROM users WHERE name = ? AND user_type = ? ;";
		PreparedStatement p;
		String username = "";
		try {
			p = con.prepareStatement(query);
			p.setString(1, name);
			p.setInt(2, (userType & 7));
			ResultSet rs = p.executeQuery();
			if (rs.next())
				username = rs.getString("username");
		} catch (SQLException e) {
			try {
				reconnect();
				p = con.prepareStatement(query);
				p.setString(1, name);
				p.setInt(2, (userType & 7));
				ResultSet rs = p.executeQuery();
				if (rs.next())
					username = rs.getString("username");
			} catch (SQLException re) {
				throw new ConnectionException(e);
			}
		}

		return new User(name, username, userType, false);

	}

	/**
	 * Generates a document Id for new documents. It gets a list of all Id's,
	 * and then adds one to the highest number and uses that as the new docId
	 * 
	 * @return
	 * @throws SQLException
	 */
	public String generateDocId() throws ConnectionException {
		String query = "SELECT * FROM documents";

		PreparedStatement p;
		ArrayList<Integer> docIds;
		try {
			p = con.prepareStatement(query);
			ResultSet rs = p.executeQuery();
			docIds = new ArrayList<Integer>();
			while (rs.next()) {
				docIds.add(Integer.parseInt(rs.getString("doc_id")));
			}
		} catch (SQLException e) {
			try {
				reconnect();
				p = con.prepareStatement(query);
				ResultSet rs = p.executeQuery();
				docIds = new ArrayList<Integer>();
				while (rs.next()) {
					docIds.add(Integer.parseInt(rs.getString("doc_id")));
				}
			} catch (SQLException re) {
				throw new ConnectionException(e);
			}
		}

		Integer max = 0;
		if (!docIds.isEmpty())
			max = Collections.max(docIds);
		max++;

		return Integer.toString(max);

	}

	/**
	 * Adds the document to the documents table in the database to map the
	 * document with a particular user
	 * 
	 * @param username
	 * @param docId
	 * @param docType
	 * @throws SQLException
	 */
	public void addToDocuments(String username, String docId, String docType)
			throws ConnectionException {
		String query = "INSERT INTO documents VALUES ( ? , ? , ? );";
		PreparedStatement p;
		try {
			p = con.prepareStatement(query);
			p.setString(1, username);
			p.setString(2, docId);
			p.setString(3, docType);
			p.executeUpdate();
		} catch (SQLException e) {
			try {
				reconnect();
				p = con.prepareStatement(query);
				p.setString(1, username);
				p.setString(2, docId);
				p.setString(3, docType);
				p.executeUpdate();
			} catch (SQLException re) {
				throw new ConnectionException(e);
			}
		}

	}

	/**
	 * Reconnect to the database
	 * 
	 * @throws ConnectionException
	 */
	public void reconnect() throws ConnectionException {
		logger.log(Level.INFO, "Attempted to reconnect to server.");
		try {
			con = DriverManager.getConnection(
					"jdbc:mysql://james.shroffabhishek.com/doctorsoffice"
							+ "?holdResultsOpenOverStatementClose=true",
					"doctorsoffice", "doctorsoffice4Upt4hMQw7FK3dZM");
		} catch (SQLException e) {
			throw new ConnectionException(e);
		}
	}

	/**
	 * List the documents associated with the given user
	 * 
	 * @param username
	 * @return
	 * @throws ConnectionException
	 */
	public List<UserDocument> getDocuments(String username)
			throws ConnectionException {
		ArrayList<UserDocument> results = new ArrayList<UserDocument>();

		ResultSet rs = null;
		String query = "SELECT * FROM documents WHERE username = ? ;";
		PreparedStatement p;
		try {
			p = con.prepareStatement(query);
			p.setString(1, username);
			rs = p.executeQuery();
			if (rs != null) {
				while (rs.next()) {
					results.add(makeDocument(rs.getString("doc_id"),
							rs.getString("doc_type")));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return results;
	}

	/**
	 * Makes a document object that represents information in the database
	 * associated with a particular Id
	 * 
	 * @param docId
	 *            the document's Id
	 * @param docType
	 *            the type of document it is
	 * @return the document
	 */
	public UserDocument makeDocument(String docId, String docType) {
		String query;
		PreparedStatement p;
		ResultSet rs = null;
		User u = new User("", "", 0, false);
		UserDocument doc = new UserDocument(docId, docType);
		System.out.println(docType);

		// if the document is an appointment, get the info from the appointments
		// table
		if (docType.equals("appointments")) {
			query = "SELECT * FROM appointments WHERE doc_id = ? ;";
			String r = "", username = "";
			long dt = 0;
			try {
				p = con.prepareStatement(query);
				p.setString(1, docId);

				rs = p.executeQuery();
				// gets the information from the database
				if (rs != null && rs.next()) {
					dt = rs.getLong("date_time");
					r = rs.getString("reason");
					username = rs.getString("desired_doctor");
				}

				query = "SELECT * FROM users WHERE username = ? ;";

				p = con.prepareStatement(query);
				p.setString(1, username);
				rs = p.executeQuery();

				// gets the doctor associated with the appointment
				if (rs != null) {
					while (rs.next()) {
						String name = rs.getString("name");
						int user_type = rs.getInt("user_type");
						u = new User(name, username, (user_type & 7),
								(user_type >> 3) >= 3);
					}
				}

				// creates the new appointment
				// doc = new Appointment(docId,d,t,r,u);
				return new Appointment(docId, dt, r, u);

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		// if the document is a doctors order
		else if (docType.equals("doctors_orders")) {
			query = "SELECT * FROM doctors_orders WHERE doc_id = ? ;";
			try {
				p = con.prepareStatement(query);
				p.setString(1, docId);

				rs = p.executeQuery();

				// get the information from the database and create the doctors
				// order
				if (rs != null) {
					while (rs.next()) {
						return new DoctorsOrder(docId,
								rs.getString("prescription"),
								rs.getString("lab_work_performed"),
								rs.getString("followup_instructions"),
								rs.getString("other_instructions"));
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		// if the document type is a treatment record
		else if (docType.equals("treatment_records")) {
			String[] stringData = new String[8];
			double[] doubleData = new double[8];
			long dateTime = 0;
			User doct = new User("", "", 1, false);
			User nurse = new User("", "", 2, false);
			UserDocument docO, inv;
			query = "SELECT * FROM treatment_records WHERE doc_id = ? ;";
			try {
				p = con.prepareStatement(query);
				p.setString(1, docId);

				rs = p.executeQuery();
				if (rs != null && rs.next()) {
					dateTime = rs.getLong("date_time");
					stringData[0] = rs.getString("attending_doctor");
					stringData[1] = rs.getString("attending_nurse");
					doubleData[0] = rs.getDouble("blood_pressure");
					doubleData[1] = rs.getDouble("pulse");
					doubleData[2] = rs.getDouble("temperature");
					doubleData[3] = rs.getDouble("height");
					doubleData[4] = rs.getDouble("weight");
					stringData[2] = rs.getString("chief_complaint");
					stringData[3] = rs.getString("diagnosis");
					stringData[4] = rs.getString("doctors_order");
					stringData[5] = rs.getString("invoice");
				}

				doct = getUser(stringData[0], User.DOCTOR);
				nurse = getUser(stringData[1], User.NURSE);
				docO = makeDocument((String) stringData[4], "doctors_orders");
				inv = makeDocument((String) stringData[5], "invoices");
				System.out.println(inv);

				
				if (docO instanceof DoctorsOrder && inv instanceof Invoice) {
					return new TreatmentRecord(docId, dateTime,
							doct, nurse, doubleData[0],
							doubleData[1], doubleData[2], doubleData[3],
							doubleData[4], stringData[2], stringData[3],
							(DoctorsOrder) docO, (Invoice)inv);
				}
				else if(docO instanceof DoctorsOrder){
					return new TreatmentRecord(docId, dateTime,
							doct, nurse, doubleData[0],
							doubleData[1], doubleData[2], doubleData[3],
							doubleData[4], stringData[2], stringData[3],
							(DoctorsOrder) docO, null);
				}
				else if(inv instanceof Invoice){
					return new TreatmentRecord(docId, dateTime,
							doct, nurse, doubleData[0],
							doubleData[1], doubleData[2], doubleData[3],
							doubleData[4], stringData[2], stringData[3],
							null, (Invoice)inv);
				}
				else{
					return new TreatmentRecord(docId, dateTime,
							doct, nurse, doubleData[0],
							doubleData[1], doubleData[2], doubleData[3],
							doubleData[4], stringData[2], stringData[3],
							null, null);
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (ConnectionException e) {
				e.printStackTrace();
			}
		}
		
		else if (docType.equals("invoices")) {
			query = "SELECT * FROM invoices WHERE doc_id = ? ;";
			try {
				p = con.prepareStatement(query);
				p.setString(1, docId);

				rs = p.executeQuery();
				if (rs != null) {
					while (rs.next()) {
						return new Invoice(docId, rs.getString("bill_items"), rs.getLong("date_time"));
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		else if (docType.equals("patient_informations")) {
			query = "SELECT * FROM patient_informations WHERE doc_id = ? ;";
			try {
				p = con.prepareStatement(query);
				p.setString(1, docId);

				rs = p.executeQuery();
				
				// get the information from the database and create the doctors
				// order
				if (rs != null) {
					while (rs.next()) {
						System.out.println("making the patient information");
						return new PatientInformation(docId,
								rs.getString("name"),
								rs.getString("address"),
								rs.getString("phone"),
								rs.getString("email"),
								rs.getString("gender"),
								rs.getInt("age"),
								rs.getString("pharmacy"),
								rs.getString("insurance"),
								rs.getString("allergies"));
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return doc;
	}

	/**
	 * Adds documents to the database and ensures that there are no duplicates
	 * id's being used
	 * 
	 * @param d
	 *            A document
	 * @param username
	 *            The user who holds the document
	 * @throws ConnectionException
	 */
	public void addDocument(UserDocument d, String username)
			throws ConnectionException {
		d.setDocId(generateDocId());
		updateDocument(d, username);
	}

	public void updateDocument(UserDocument d, String username)
			throws ConnectionException {

		String docType = d.getDocType();

		deleteDocument(d); // delete the document from all tables

		// add to the appointments table
		if (docType.equals("appointments")) {

			PreparedStatement p;

			Appointment a = (Appointment) d;

			String query = "INSERT INTO appointments VALUES( ? , ? , ? , ? );";
			try {
				p = con.prepareStatement(query);
				p.setString(1, a.getDocId());
				p.setLong(2, a.getDateTime());
				p.setString(3, a.getReason());
				p.setString(4, a.getDesiredDoctor().getUsername());
				p.executeUpdate();
			} catch (SQLException e) {
				throw new ConnectionException(e);
			}

			addToDocuments(username, a.getDocId(), "appointments");
		}

		// adds doctors_orders
		else if (docType.equals("doctors_orders")) {

			PreparedStatement p;

			DoctorsOrder doco = (DoctorsOrder) d;

			String query = "INSERT INTO doctors_orders VALUES( ? , ? , ? , ? , ?);";
			try {
				p = con.prepareStatement(query);
				p.setString(1, doco.getDocId());
				p.setString(2, doco.getPrescription());
				p.setString(3, doco.getLabWorkPerformed());
				p.setString(4, doco.getFollowUpInstructions());
				p.setString(5, doco.getOtherInstructions());
				p.executeUpdate();
			} catch (SQLException e) {
				throw new ConnectionException(e);
			}

			addToDocuments(username, doco.getDocId(), "doctors_orders");
		} else if (docType.equals("patient_informations")) {

			PatientInformation pi = (PatientInformation) d;
			PreparedStatement p;
			String query = "INSERT INTO patient_informations VALUES( ? , ? , ? , ? , ?, ?, ?, ?, ?, ?);";
			try {
				p = con.prepareStatement(query);
				p.setString(1, pi.getDocId());
				p.setString(2, pi.getName());
				p.setString(3, pi.getAddress());
				p.setString(4, pi.getPhone());
				p.setString(5, pi.getEmail());
				p.setString(6, pi.getGender());
				p.setInt(7, pi.getAge());
				p.setString(8, pi.getPharmacy());
				p.setString(9, pi.getInsurance());
				p.setString(10, pi.getAllergies());
				p.executeUpdate();
			} catch (SQLException e) {
				throw new ConnectionException(e);
			}

			addToDocuments(username, pi.getDocId(), "patient_informations");
		}

		else if (docType.equals("treatment_records")) {
			PreparedStatement p;
			TreatmentRecord t = (TreatmentRecord) d;

			String query = "INSERT INTO treatment_records VALUES( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?);";
			try {
				reconnect();
				p = con.prepareStatement(query);
				p.setString(1, t.getDocId());
				p.setLong(2, t.getDateTime());
				if (t.getAttendingDoctor() != null)
					p.setString(3, t.getAttendingDoctor().getUsername());
				else
					p.setString(3, "");
				if (t.getAttendingNurse() != null)
					p.setString(4, t.getAttendingNurse().getUsername());
				else
					p.setString(4, "");
				p.setDouble(5, t.getBloodPressure());
				p.setDouble(6, t.getPulse());
				p.setDouble(7, t.getTemperature());
				p.setDouble(8, t.getHeight());
				p.setDouble(9, t.getWeight());
				p.setString(10, t.getChiefComplaint());
				p.setString(11, t.getDiagnosis());
				if (t.getDoctorsOrder() != null)
					p.setString(12, t.getDoctorsOrder().getDocId());
				else
					p.setString(12, "");
				if (t.getInvoice() != null)
					p.setString(13, t.getInvoice().getDocId());
				else
					p.setString(13, "");
				p.executeUpdate();
			} catch (SQLException e) {
				throw new ConnectionException(e);
			}

			addToDocuments(username, t.getDocId(), "treatment_records");
		}
		
		if (docType.equals("invoices")) {

			PreparedStatement p;

			Invoice i = (Invoice) d;

			String query = "INSERT INTO invoices VALUES( ? , ?, ?);";
			try {
				p = con.prepareStatement(query);
				p.setString(1, i.getDocId());
				p.setString(2, i.getBillItems());
				p.setLong(3, i.getDate());
				p.executeUpdate();
			} catch (SQLException e) {
				throw new ConnectionException(e);
			}

			addToDocuments(username, i.getDocId(), "invoices");
		}
	}

	/**
	 * Delete the document from the database
	 * 
	 * @param d
	 *            UserDocument to delete
	 * @throws ConnectionException
	 */
	public void deleteDocument(UserDocument d) throws ConnectionException {

		String docType = d.getDocType();
		String docId = d.getDocId();

		// remove from the documents table
		String deleteQuery = "DELETE FROM documents where doc_id = ? ;";
		PreparedStatement p2;
		try {
			p2 = con.prepareStatement(deleteQuery);
			p2.setString(1, docId);
			p2.executeUpdate();
		} catch (SQLException e) {
			throw new ConnectionException(e);
		}

		// remove from the appointment table if its an appointment
		if (docType.equals("appointments")) {
			deleteQuery = "DELETE FROM appointments where doc_id = ? ;";
			PreparedStatement p;
			try {
				p = con.prepareStatement(deleteQuery);
				p.setString(1, docId);
				p.executeUpdate();
			} catch (SQLException e) {
				throw new ConnectionException(e);
			}
		}

		// remove from the patient information table if its a patient
		// information document
		else if (docType.equals("patient_informations")) {
			deleteQuery = "DELETE FROM patient_informations where doc_id = ? ;";
			PreparedStatement p;
			try {
				p = con.prepareStatement(deleteQuery);
				p.setString(1, docId);
				p.executeUpdate();
			} catch (SQLException e) {
				throw new ConnectionException(e);
			}

		}

		// remove from the treatment records table if it is a treatment record
		else if (docType.equals("treatment_records")) {
			deleteQuery = "DELETE FROM treatment_records where doc_id = ? ;";
			PreparedStatement p;
			try {
				p = con.prepareStatement(deleteQuery);
				p.setString(1, docId);
				p.executeUpdate();
			} catch (SQLException e) {
				throw new ConnectionException(e);
			}
		}

		// delete from doctors orders if it is a doctors order
		else if (docType.equals("doctors_orders")) {
			deleteQuery = "DELETE FROM doctors_orders where doc_id = ? ;";
			PreparedStatement p;
			try {
				p = con.prepareStatement(deleteQuery);
				p.setString(1, docId);
				p.executeUpdate();
			} catch (SQLException e) {
				throw new ConnectionException(e);
			}
		}
		
		else if (docType.equals("invoices")) {
			deleteQuery = "DELETE FROM invoices where doc_id = ? ;";
			PreparedStatement p;
			try {
				p = con.prepareStatement(deleteQuery);
				p.setString(1, docId);
				p.executeUpdate();
			} catch (SQLException e) {
				throw new ConnectionException(e);
			}
		}
	} // end delete document
}
